package com.charging.business.qin;

import com.charging.business.config.MinFeeManager;
import com.charging.business.domain.Arfd;
import com.charging.business.exception.CustomizeException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author JerryLu
 * @date 2024/11/27 15:44
 * @description 陕西db3文件读取加载工具类
 */
@Service
public class Db3FileUtils {

    private final MinFeeManager minFeeManager;
    @Value("${database.folder.path}")
    private String folderPath;

    @Autowired
    public Db3FileUtils(MinFeeManager minFeeManager) {
        this.minFeeManager = minFeeManager;
    }

    public void loadDb3File(String enStationId, String exStationId, Integer vehicleType) throws Exception {
        File folder = new File(folderPath);
        File[] listOfFiles = folder.listFiles((dir, name) -> name.matches(".*_*\\.db3$"));

        if (listOfFiles == null || listOfFiles.length == 0) {
            throw new CustomizeException("本地费率库缺失");
        }

        // 提取版本号并找到最新版本
        Map<String, File> versionToFileMap = new HashMap<>();
        for (File file : listOfFiles) {
            String fileName = file.getName();
            Pattern pattern = Pattern.compile("_(\\d{14})_");
            Matcher matcher = pattern.matcher(fileName);
            if (matcher.find()) {
                String version = matcher.group(1);
                versionToFileMap.put(version, file);
            }
        }
        // 确定当前应该使用的版本
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        String currentVersion = "";
        for (String version : versionToFileMap.keySet()) {
            LocalDateTime versionTime = LocalDateTime.parse(version, formatter);
            if (versionTime.isAfter(now) || versionTime.isEqual(now)) {
                currentVersion = version;
                break;
            }
        }

        if (currentVersion.isEmpty()) {
            currentVersion = Collections.max(versionToFileMap.keySet()); // 使用最新的版本
        }
        Map<String, List<Arfd>> dbMinFee = new ConcurrentHashMap<>();
        File latestFile = versionToFileMap.get(currentVersion);

        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:" + latestFile.getAbsolutePath())) {
            String sql = "SELECT * FROM Arfd where enID = ? and exID = ? and vType = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, enStationId);
                pstmt.setString(2, exStationId);
                pstmt.setInt(3, vehicleType);

                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    String key = String.format("%s|%s|%02d", rs.getString("enID"), rs.getString("exID"), rs.getInt("vType"));

                    Arfd model = new Arfd();
                    // 根据实际情况填充model对象
                    model.setEnProv(rs.getString("enProv"));
                    model.setEnID(rs.getString("enID"));
                    model.setExProv(rs.getString("exProv"));
                    model.setExID(rs.getString("exID"));
                    model.setMilesTotal(rs.getString("milesTotal"));
                    model.setSIndex(rs.getInt("sIndex"));
                    model.setProv(rs.getString("prov"));
                    model.setCount(rs.getInt("count"));
                    model.setSGroup(rs.getString("sGroup"));
                    model.setMVer(rs.getString("mVer"));
                    model.setPVer(rs.getString("pVer"));
                    model.setMilesProv(rs.getInt("milesProv"));
                    model.setVType(rs.getInt("vType"));
                    model.setFeeGroup(rs.getString("feeGroup"));
                    model.setTollFee(rs.getInt("tollFee"));
                    model.setPFee(rs.getInt("PFee"));
                    model.setFee(rs.getInt("Fee"));
                    model.setFee95(rs.getInt("Fee95"));
                    model.setPFee95(rs.getInt("PFee95"));
                    model.setVersion(!currentVersion.isEmpty() ? currentVersion : null);

//                        allData.add(model);
                    List<Arfd> list = dbMinFee.get(key);
                    if (list == null) {
                        list = new ArrayList<>();
                        dbMinFee.put(key, list);
                    }
                    list.add(model);
                }
            }
        }
        //更新缓存
        this.minFeeManager.setMinFeeVersion(currentVersion);
        this.minFeeManager.updateFeeRates(currentVersion, dbMinFee);
    }
}
